﻿using DBUtil;
using DBUtil.Provider.SqlServer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using DotNetCommon.Extensions;
using System.Linq;
using NUnit.Framework;
using Shouldly;
using System.Threading.Tasks;

namespace Test.SqlServer.Curd.Insert
{
	[TestFixture]
	public sealed class InsertTestsAsync : TestBase
	{
		#region 复杂数据类型
		[Test]
		public async Task InsertComplexColumnTypeTest()
		{
			PrepareColumnTypes();
			//插入
			var res = await db.InsertAsync("test", new Test
			{
				col_bigint = 2,
				col_bit = true,
				col_binary = System.Text.Encoding.UTF8.GetBytes("小明"),
				col_varchar = "天明路"
			}.ToDictionary().RemoveFluent(nameof(Test.col_timestamp)));
			res.ShouldBe(1);
			var person = db.SelectModel<Test>("select * from test order by col_timestamp desc");
			person.ShouldNotBeNull();
			person.col_bigint.ShouldBe(2);
			person.col_bit.ShouldBe(true);
			System.Text.Encoding.UTF8.GetString(person.col_binary).Trim().ShouldStartWith("小明\0\0");
			person.col_varchar.ShouldBe("天明路");
		}
		#endregion

		[Test]
		public async Task InsertTest()
		{
			//准备表
			if (db.IsTableExist("test"))
			{
				db.Manage.DropTable("test");
			}
			db.ExecuteSql("create table test(id int identity(1,1) not null,name varchar(50),age int)");
			//插入null
			var dic = new Dictionary<string, object>();
			dic.Add("name", null);
			var res = await db.InsertAsync("test", dic);
			res.ShouldBe(1);
			var model = db.SelectDictionary("select * from test");
			model.ShouldNotBeNull();
			model["id"].ShouldBe(1);
			model["name"].ShouldBe(DBNull.Value);

			//正常插入一条
			db.Manage.TruncateTable("test");
			res = await db.InsertAsync("test", new { name = "小明", age = 18 }.ToDictionary());
			res.ShouldBe(1);
			model = db.SelectDictionary("select * from test");
			model.ShouldNotBeNull();
			model["id"].ShouldBe(1);
			model["name"].ShouldBe("小明");
			model["age"].ShouldBe(18);

			//插入两条
			db.Manage.TruncateTable("test");
			res = await db.InsertAsync("test", new IDictionary<string, object>[]{
				new { name = "小明", age = 18 }.ToDictionary(),
				new { name = "小王", age = 20 }.ToDictionary()
			});
			res.ShouldBe(2);
			var models = db.SelectDictionaryList("select * from test");
			models.Count.ShouldBe(2);
			models[0]["id"].ShouldBe(1);
			models[0]["name"].ShouldBe("小明");
			models[0]["age"].ShouldBe(18);
			models[1]["id"].ShouldBe(2);
			models[1]["name"].ShouldBe("小王");
			models[1]["age"].ShouldBe(20);

			//插入两条,但是列不同
			db.Manage.TruncateTable("test");
			res = await db.InsertAsync("test", new IDictionary<string, object>[]{
				new { name = "小明", age = 18 }.ToDictionary(),
				new { name = "小王" }.ToDictionary()
			});
			res.ShouldBe(2);
			models = db.SelectDictionaryList("select * from test");
			models.Count.ShouldBe(2);
			models[0]["id"].ShouldBe(1);
			models[0]["name"].ShouldBe("小明");
			models[0]["age"].ShouldBe(18);
			models[1]["id"].ShouldBe(2);
			models[1]["name"].ShouldBe("小王");
			models[1]["age"].ShouldBe(DBNull.Value);
		}

		[Test]
		public async Task InsertBatchTest()
		{
			//准备表
			if (db.IsTableExist("test"))
			{
				db.Manage.DropTable("test");
			}
			db.ExecuteSql("create table test(id int identity(1,1) not null,name varchar(50),age int)");
			if (db.IsTableExist("test2"))
			{
				db.Manage.DropTable("test2");
			}
			db.ExecuteSql("create table test2(id int identity(1,1) not null,name varchar(50),age int)");

			//一个表插入两条
			var res = await db.InsertBatchAsync(new InsertBatchItem[] {
				InsertBatchItem.Create("test", new { name = "小明" }.ToDictionary()),
				InsertBatchItem.Create("test", new { name = "小王", age = 20 }.ToDictionary())
			});
			res.ShouldBe(2);
			var models = db.SelectDictionaryList("select  * from test");
			models.Count.ShouldBe(2);
			models[0]["id"].ShouldBe(1);
			models[0]["name"].ShouldBe("小明");
			models[0]["age"].ShouldBe(DBNull.Value);
			models[1]["id"].ShouldBe(2);
			models[1]["name"].ShouldBe("小王");
			models[1]["age"].ShouldBe(20);

			//两个表插入两条
			db.Manage.TruncateTable("test");
			db.Manage.TruncateTable("test2");
			res = await db.InsertBatchAsync(new InsertBatchItem[] {
			   InsertBatchItem.Create("test", new { name = "小明" }.ToDictionary()),
			   InsertBatchItem.Create("test2", new { name = "小王", age = 20 }.ToDictionary())
			});
			res.ShouldBe(2);
			var model = db.SelectDictionary("select  * from test");
			model["id"].ShouldBe(1);
			model["name"].ShouldBe("小明");
			model["age"].ShouldBe(DBNull.Value);

			model = db.SelectDictionary("select  * from test2");
			model["id"].ShouldBe(1);
			model["name"].ShouldBe("小王");
			model["age"].ShouldBe(20);
		}

		[Test]
		public async Task InsertAndGetIdTest()
		{
			//准备表
			if (db.IsTableExist("test"))
			{
				db.Manage.DropTable("test");
			}
			db.ExecuteSql("create table test(id int identity(1,1) not null,name varchar(50) not null)");
			//开始插入并获取自增Id
			long id = await db.InsertAndGetIdAsync("test", new { name = "小明" }.ToDictionary());
			id.ShouldBe(1);
			id = await db.InsertAndGetIdAsync("test", new { name = "小王" }.ToDictionary());
			id.ShouldBe(2);
			var models = db.SelectDictionaryList("select * from test");
			models.Count.ShouldBe(2);
			models[0]["id"].ShouldBe(1);
			models[0]["name"].ShouldBe("小明");
			models[1]["id"].ShouldBe(2);
			models[1]["name"].ShouldBe("小王");
		}

		[Test]
		public async Task InsertAndGetIdsTest()
		{
			//准备表
			if (db.IsTableExist("test"))
			{
				db.Manage.DropTable("test");
			}
			db.ExecuteSql("create table test(id int identity(1,1) not null,name varchar(50) not null)");

			//先批量插入,使test和t_test的自增id不相等
			var res = await db.InsertAsync("test", new IDictionary<string, object>[]{
				new { name = "小明" }.ToDictionary(),
				new { name = "小王" }.ToDictionary()
			});
			res.ShouldBe(2);
			var models = db.SelectDictionaryList("select * from test");
			models.Count.ShouldBe(2);

			//插入并获取自增Id
			//准备触发器另外一张表,当向test表插入数据时,触发向 t_test 表插入一条数据
			if (db.IsTableExist("t_test"))
			{
				db.Manage.DropTable("t_test");
			}
			db.ExecuteSql("create table t_test(id int identity(1,1) not null,name varchar(50))");
			if (db.IsTriggerExist("tri_test_after_insert"))
			{
				db.Manage.DropTrigger("tri_test_after_insert");
			}
			db.ExecuteSql("create trigger tri_test_after_insert on test after insert as begin insert into t_test(name) values('xiaohua');end");
			//开始插入并获取自增Id
			var ids = await db.InsertAndGetIdsAsync("test", new IDictionary<string, object>[]{
				new Dictionary<string, object>() { { "name", "张三" } },
				new Dictionary<string, object>() { { "name", "李四" } }
			});
			Assert.IsTrue("3,4" == string.Join(",", ids));

			models = db.SelectDictionaryList("select * from t_test");
			models.Count.ShouldBe(1);
			models[0]["id"].ShouldBe(1);

			ids = await db.InsertAndGetIdsAsync("test", new IDictionary<string, object>[]{
				new { name = "hi" }.ToDictionary(),
				new { name = "pl" }.ToDictionary()
			});
			Assert.IsTrue("5,6" == string.Join(",", ids));

			ids = await db.InsertAndGetIdsAsync("test", new IDictionary<string, object>[]{
				new Dictionary<string, object>() { { "name", "ji" } },
				new Dictionary<string, object>() { { "name", "huji" } }
			});
			Assert.IsTrue("7,8" == string.Join(",", ids));

			models = db.SelectDictionaryList("select * from t_test");
			models.Count.ShouldBe(3);
			models[2]["id"].ShouldBe(3);
		}

		[Test]
		public async Task InsertNotParameterizationTest()
		{
			//准备表
			if (db.IsTableExist("test"))
			{
				db.Manage.DropTable("test");
			}
			db.ExecuteSql("create table test(id int primary key,name varchar(50))");
			if (db.IsTableExist("testmoney"))
			{
				db.Manage.DropTable("testmoney");
			}
			db.ExecuteSql("create table testmoney(id int primary key,money bigint)");
			await db.InsertAsync("testmoney", new { id = 1, money = 100 }.ToDictionary());
			await db.InsertAsync("testmoney", new { id = 2, money = 200 }.ToDictionary());

			var res = await db.InsertAsync("testmoney", new
			{
				id = 3,
				money = new RawString("(select sum(money) from testmoney)")
			}.ToDictionary());

			res.ShouldBe(1);

			var model = db.SelectDictionary("select * from testmoney where id=3");
			model["money"].ShouldBe(300);
		}

		#region 测试批量插入数据 SqlBulkCopy
		[Test]
		public async Task SqlBulkCopyTest()
		{
			db.Manage.DropTableIfExist("test");
			db.ExecuteSql(@"
        create table test(
            id int primary key,
            name varchar(50),
            age int,
            birth datetime,
            email varchar(50),
            addr varchar(200)
        )");
			var dt = new DataTable("test");
			dt.Columns.Add("id");
			dt.Columns.Add("name");
			dt.Columns.Add("age");
			dt.Columns.Add("birth");
			dt.Columns.Add("email");
			dt.Columns.Add("addr");
			//10万条数据
			var ra = new Random();
			var count = 10 * 10000;
			for (int i = 0; i < count; i++)
			{
				var row = dt.NewRow();
				row["id"] = i + 1;
				var name = $"username{i + 1}";
				row["name"] = name;
				var age = ra.Next(10, 60);
				row["age"] = age;
				var birth = DateTime.Parse(DateTime.Now.AddYears(-age).ToString("yyyy-MM-dd"));
				row["birth"] = birth;
				var email = $"{name}@qq.com";
				row["email"] = email;
				var addr = $"{name}天明路";
				row["addr"] = addr;
				dt.Rows.Add(row);
			}
			Stopwatch stopwatch = new Stopwatch();
			stopwatch.Start();
			TimeSpan span = TimeSpan.MinValue;
			await ((SqlServerAccess)db).BulkCopyAsync(dt);
			span = stopwatch.Elapsed;
			stopwatch.Stop();
			Console.WriteLine($"插入{count}条,耗时:{span.TotalMilliseconds}毫秒，或{span.TotalSeconds}秒，或{span.TotalMinutes}分钟");
		}
		#endregion
	}
}
